package com.lanhai.agv;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lanhai.agv.cache.AgvItem;
import com.lanhai.agv.cache.ParkItem;
import com.lanhai.agv.cache.ParkRelationItem;
import com.lanhai.agv.cache.ParkTransPosEnum;
import com.lanhai.agv.cache.ParkTypeEnum; 
import com.lanhai.agv.cache.RoadItem;
import com.lanhai.agv.cache.TranfficArea;
 


public class DBHelper {
	String connstr;
	String connuser;
	String connpwd;

	public DBHelper(String connStr, String user, String password) throws Exception {
		 
		Class.forName("com.mysql.cj.jdbc.Driver");
		this.connstr = connStr;
		this.connuser = user;
		this.connpwd = password;
		
		
	}

	public List<AgvItem> getAgvList() throws Exception {
		List<AgvItem> arr = new ArrayList<AgvItem>();
		String sql = "select agvid,ip,port from agvinfo";
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int agvid = resultSet.getInt("agvid");
				int port = resultSet.getInt("port");
				String ip = resultSet.getString("ip");
				AgvItem obj = new AgvItem();
				obj.setAgvId(agvid);
				obj.setPort(port);
				obj.setIP(ip);
				arr.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return arr;
	}

	/**
	 * 获取交管区域
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<TranfficArea> getTranfficAreaList() throws Exception {
		List<TranfficArea> arr = new ArrayList<TranfficArea>();
		String sql = "select areaid,minx,maxx,miny,maxy,areatype from trafficarea";
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int areaid = resultSet.getInt("areaid");
				int minx = resultSet.getInt("minx");
				int maxx = resultSet.getInt("maxx");
				int miny = resultSet.getInt("miny");
				int maxy = resultSet.getInt("maxy");
				int areatype=resultSet.getInt("areatype");

				TranfficArea obj = new TranfficArea();
				obj.setAreaId(areaid);
				obj.setMinX(minx);
				obj.setMaxX(maxx);
				obj.setMinY(miny);
				obj.setMaxY(maxy);
				obj.setAreaType(areatype); 
				arr.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return arr;
	}

	/**
	 * 获取工序分组名称
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<String> getProcessGroup() throws Exception {
		String sql = "select DISTINCT processgroup from stationinfo";
		List<String> arr = new ArrayList<String>();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String processgroup = resultSet.getString("processgroup");
				arr.add(processgroup);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return arr;
	}

	/**
	 * 停泊位，对接设备和停泊的路线信息
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<ParkItem> getParkList() throws Exception {
		String sql = "select p.parkno,p.ptype,p.roadids, p.x,p.y,p.selecttype, "
				+ "s.stationip,s.stationport,s.cmdsign,s.processgroup,s.stationgroup,s.transtype,s.nextprocess,"
				+ "s.uptrans,s.downtrans,s.weight,s.isdisable,p.readydotarr,p.releasedot ,p.tag"  
				+ "from parkinfo p " 
				+ "left join stationinfo s on p.stationid =s.stationid ";
		List<ParkItem> arr = new ArrayList<ParkItem>();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int parkno = resultSet.getInt("parkno");
				int ptype = resultSet.getInt("ptype"); 
				int x = resultSet.getInt("x");
				int y = resultSet.getInt("y");
				int selecttype = resultSet.getInt("selecttype"); 
				String stationip = resultSet.getString("stationip");
				int stationport = resultSet.getInt("stationport");
				String cmdsign = resultSet.getString("cmdsign");
				String processgroup = resultSet.getString("processgroup");
				String stationgroup = resultSet.getString("stationgroup");
				int transtype = resultSet.getInt("transtype");
				int weight = resultSet.getInt("weight");
				boolean uptrans = resultSet.getBoolean("uptrans");
				boolean downtrans = resultSet.getBoolean("downtrans");
				boolean isdisable = resultSet.getBoolean("isdisable"); 
				String readydotarr= resultSet.getString("readydotarr");
			 int releasedot= resultSet.getInt("releasedot"); 
			 String nextprocess=resultSet.getString("nextprocess");
			 String tag=resultSet.getString("tag");
					
				ParkItem obj = new ParkItem();
				obj.setParkNo(parkno);
				obj.setSourceRoad(0);
				obj.setTargetRoad(0);
				obj.setArea(x, y);
				obj.setStationUpTrans(uptrans);
				obj.setStationDownTrans(downtrans);
				obj.setWeight(weight);
				obj.setProcessGroup(processgroup);
				obj.setCmdSign(cmdsign);
				obj.setStationGroup(stationgroup);
				obj.setStationIP(stationip);
				obj.setStationPort(stationport); 
				 
				obj.setSelectType(selecttype);
				obj.setTransType(ParkTransPosEnum.getParkTransPosEnum(transtype));
				obj.setType(ParkTypeEnum.getParkTypeEnum(ptype));
				obj.setStationDisable(isdisable);
				obj.setReadydotarr(readydotarr);
				obj.setReleaseDot(releasedot);
				obj.setNextprocess(nextprocess);
				obj.setTag(tag);
				arr.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return arr;
	}

	//路线和工序和对应项RoadCache 
	public List<RoadItem> getRoad () throws Exception {
		String sql = "select roadid,  sourceparkno,targetparkno,lv from roadinfo ";
		List<RoadItem> arr = new ArrayList<RoadItem>();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) { 
				int road=resultSet.getInt("roadid"); 
				int targetparkno=resultSet.getInt("targetparkno");
				int sourceparkno=resultSet.getInt("sourceparkno");
				int lv=resultSet.getInt("lv");
				
				RoadItem obj=new RoadItem(); 
				obj.setSourceParkNo(sourceparkno);
				obj.setTargetParkNo(targetparkno);
				obj.setRoadId(road); 
				obj.setLv(lv);
				arr.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} 
		return arr;
	}

	
	public List<ParkRelationItem> getEstimateParkRelaction() throws Exception{
		//预判断点-路线-装卸分配点
		String sql = " select s.parkno as s1,e.parkno as e1 from  parkinfo s,roadinfo r,parkinfo e"
				+ " where r.sourceparkno=s.parkno and r.targetparkno=e.parkno and s.ptype=6 and e.ptype=4";
		List<ParkRelationItem> arr = new ArrayList<ParkRelationItem>();
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connstr, connuser, connpwd);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) { 
				int s1=resultSet.getInt("s1"); 
				int e1=resultSet.getInt("e1"); 
				
				ParkRelationItem obj=new ParkRelationItem(); 
				 obj.setEnddot(e1);
				 obj.setStartdot(s1);
				arr.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} 
		return arr;
	}
}
